#!/usr/bin/env python
# -*- coding:utf-8 -*-

# file:do_exam1.py
# author:王雯雯
# datetime:2024-11-12 
# software: PyCharm

import pymysql
from DBUtils.PooledDB import PooledDB
from..config.database_config import config
from..models.department_model import Department


pool = PooledDB(
    creator=pymysql,
    **config,
    maxconnections=10,
    mincached=2,
    blocking=True
)


# 根据院系编号查询院系信息
def query_department_by_id(id):
    try:
        connection = pool.connection()
        cursor = connection.cursor()
        sql = "SELECT * FROM department WHERE id = %s"
        cursor.execute(sql, (id,))
        result = cursor.fetchone()
        if result:
            department = Department(*result)
            cursor.close()
            return department
        connection.close()
    except pymysql.Error as e:
        print(f"查询院系信息时出错: {e}")
    return None


# 插入院系信息
def insert_department(department):
    try:
        connection = pool.connection()
        cursor = connection.cursor()
        sql = "INSERT INTO department (id, pid, name) VALUES (%s, %s, %s)"
        cursor.execute(sql, (department.id, department.pid, department.name))
        connection.commit()
        cursor.close()
        connection.close()
        print("院系信息插入成功")
    except pymysql.Error as e:
        print(f"插入院系信息时出错: {e}")


# 根据院系编号删除院系信息
def delete_department_by_id(id):
    try:
        connection = pool.connection()
        cursor = connection.cursor()
        sql = "DELETE FROM department WHERE id = %s"
        cursor.execute(sql, (id,))
        connection.commit()
        cursor.close()
        connection.close()
        print("院系信息删除成功")
    except pymysql.Error as e:
        print(f"删除院系信息时出错: {e}")


# 根据院系名称查询院系信息（模糊查询）
def query_departments_by_name(name):
    try:
        connection = pool.connection()
        cursor = connection.cursor()
        sql = "SELECT * FROM department WHERE name LIKE %s"
        cursor.execute(sql, (f"%{name}%",))
        results = cursor.fetchall()
        departments = []
        for result in results:
            department = Department(*result)
            departments.append(department)
        cursor.close()
        connection.close()
        return departments
    except pymysql.Error as e:
        print(f"查询院系信息时出错: {e}")
    return []